<% temporaryfolder=addbs(oprop.appstartpath)+[temp\] *temporaryfolder=[temp\] fromdate=orequest.form('datefrom') todate=orequest.form('dateto') if isnull(fromdate) fromdate=NVL(orequest.querystring('datefrom'),'{}') endif if isnull(todate) todate=NVL(orequest.querystring('dateto'),'{}') endif set date ymd pdate1=ctod(fromdate) pdate2=ctod(todate) set date british usesafe('acctran') usesafe('glcodes') usesafe('accounts') openingbalancealias=sys(2015) closingbalancealias=sys(2015) SELECT [acctran] SET ORDER TO acdate retainedearnings=0 FILEstring=[

GLWISE PROFIT AND LOSS

]+CHR(13) do case case empty(pdate1) and empty(PDATE2) total to (temporaryfolder+closingbalancealias) ON accountid for val(left(accountid,2))>8 fields debit, credit copy structure to (temporaryfolder+openingbalancealias) FILEstring=FILESTRING+[

FROM START TO END

] case empty(pdate1) and not empty(PDATE2) total to (temporaryfolder+closingbalancealias) ON accountid for val(left(accountid,2))>8 and date<=pdate2 fields debit, credit copy structure to (temporaryfolder+openingbalancealias) FILEstring=FILESTRING+[

FROM START TO ]+DTOC(PDATE2)+[

] case not empty(pdate1) and empty(pdate2) total to (temporaryfolder+closingbalancealias) ON accountid for val(left(accountid,2))>8 and date>=pdate1 fields debit, credit copy structure to (temporaryfolder+openingbalancealias) FILEstring=FILESTRING+[

FROM ]+DTOC(PDATE1)+[ TO END

] otherwise total to (temporaryfolder+closingbalancealias) ON accountid for val(left(accountid,2))>8 and between(date,pdate1,pdate2) fields debit, credit copy structure to (temporaryfolder+openingbalancealias) FILEstring=FILESTRING+[

FROM ]+DTOC(PDATE1)+[ TO ]+DTOC(PDATE2)+[

] ENDCASE USE ((temporaryfolder+openingbalancealias+[.dbf])) IN select(openingbalancealias) select (openingbalancealias) CALCULATE SUM(debit-credit) TO retainedearnings USE ((temporaryfolder+closingbalancealias+[.dbf])) IN select(closingbalancealias) select (closingbalancealias) CALCULATE SUM(debit-credit) TO profitfortheperiod select * from (temporaryfolder+closingbalancealias) union all select * from (temporaryfolder+openingbalancealias) order by accountid, date ; into cursor temporaycursor use in select(openingbalancealias) use in select(closingbalancealias) ERASE ((temporaryfolder+closingbalancealias+[.dbf])) ERASE ((temporaryfolder+openingbalancealias+[.dbf])) SELECT [temporaycursor] GO top STORE 0 TO acdebits, accredits, netincome, netdirect, netoffice, netincomecnt, netdirectcnt, netofficecnt filestring=FILESTRING+CHR(13)+[] theglcode=[] thisgl=[] newgl=[xxx] store space(0) to debitstr, creditstr, theglname DO WHILE NOT EOF() thisac=accountid thisgl=left(thisac,2) =seek(thisac,[accounts],[accountid]) thisacname=iif(empty(accounts.name),thisac+[ has no name],allt(accounts.name)) STORE 0 TO acbalance do while not eof() and accountid=thisac acbalance=acbalance+credit-debit skip enddo if thisgl<>left(accountid,2) newgl=left(accountid,2) endif if acbalance=0 loop endif if acbalance>0 glhead=[] creditstr=creditstr+glhead else glhead=[] debitstr=debitstr+glhead endif do Case case val(thisgl)<12 netincome=netincome+acbalance netincomecnt=netincomecnt+1 case val(thisgl)=12 netdirect=netdirect+acbalance netdirectcnt=netdirectcnt+1 otherwise netoffice=netoffice+acbalance netofficecnt=netofficecnt+1 endcase if val(thisgl)<12 and (val(newgl)>11 or eof()) do case case netincome>0 and netincomecnt>1 glhead=creditstr+DEBITSTR+[] case netincome>0 and netincomecnt=1 glhead=creditstr+DEBITSTR case netincome<0 and netincomecnt>1 glhead=DEBITSTR+creditstr+[] case netincome<0 and netincomecnt=1 glhead=DEBITSTR+creditstr endcase filestring=filestring+glhead+CHR(13) STORE SPACE(0) TO DEBITSTR, creditstr endif if val(thisgl)=12 and (val(newgl)>12 or eof()) do case case netdirect<0 and netdirectcnt>1 glhead=creditstr+DEBITSTR+[] case netdirect<0 and netdirectcnt=1 glhead=creditstr+DEBITSTR case netdirect>0 and netdirectcnt>1 glhead=DEBITSTR+creditstr+[] case netdirect>0 and netdirectcnt=1 glhead=DEBITSTR+creditstr endcase filestring=filestring+glhead+CHR(13) STORE SPACE(0) TO DEBITSTR, creditstr IF NETINCOME+NETDIRECT=0 LOOP ENDIF IF NETINCOME+NETDIRECT>0 glhead=[] filestring=filestring+glhead+CHR(13) ELSE glhead=[] filestring=filestring+glhead+CHR(13) ENDIF loop endif if eof() do case case netoffice<0 and netofficecnt>1 glhead=creditstr+DEBITSTR+[] case netoffice<0 and netofficecnt=1 glhead=creditstr+DEBITSTR case netoffice>0 and netofficecnt>1 glhead=DEBITSTR+creditstr+[] case netoffice>0 and netofficecnt=1 glhead=DEBITSTR+creditstr endcase filestring=filestring+glhead+CHR(13) STORE SPACE(0) TO DEBITSTR, creditstr IF NETINCOME+NETDIRECT+netoffice=0 LOOP ENDIF IF NETINCOME+NETDIRECT+netoffice>0 glhead=[] filestring=filestring+glhead+CHR(13) ELSE glhead=[] filestring=filestring+glhead+CHR(13) ENDIF endif ENDDO filestring=filestring+CHR(13)+[
DETAILSDEBITCREDIT
]+THISACname+[ ]+TRANSFORM(ABS(ACbalance),'999,999,999,999.99')+[
]+THISACname+[]+TRANSFORM(ABS(ACbalance),'999,999,999,999.99')+[ 
TOTAL INCOME ]+TRANSFORM(ABS(NETINCOME),'999,999,999,999.99')+[
TOTAL INCOME]+TRANSFORM(ABS(NETINCOME),'999,999,999,999.99')+[ 
LESS DIRECT EXPENSES]+TRANSFORM(ABS(netdirect),'999,999,999,999.99')+[ 
LESS DIRECT EXPENSES ]+TRANSFORM(ABS(netdirect),'999,999,999,999.99')+[
GROSS PROFIT]+TRANSFORM(ABS(NETINCOME+NETDIRECT),'999,999,999,999.99')+[ 
GROSS LOSS ]+TRANSFORM(ABS(NETINCOME+NETDIRECT),'999,999,999,999.99')+[
LESS OFFICE EXPENSES]+TRANSFORM(ABS(netoffice),'999,999,999,999.99')+[ 
LESS OFFICE EXPENSES ]+TRANSFORM(ABS(netoffice),'999,999,999,999.99')+[
NET PROFIT]+TRANSFORM(ABS(NETINCOME+NETDIRECT+netoffice),'999,999,999,999.99')+[ 
NET LOSS ]+TRANSFORM(ABS(NETINCOME+NETDIRECT+netoffice),'999,999,999,999.99')+[
] %>

<%=xcompany.name%>

<%=FILESTRING%>